package fy.hr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.hr.bean.AwayFormBean;
import fy.hr.constant.OA2HRConstant;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class AwayFormDao {
	private static Log log = LogFactory.getLog(AwayFormDao.class);

	private final static String FIND_PERSONINFO_STR = "select t.pk_psndoc, t.pk_deptdoc,t.pk_corp from bd_psndoc t where  psnclscope='0' and ";

	private final static String FIND_PK_TBM_PSNDOC_STR = "select pk_tbm_psndoc, pk_psndoc from tbm_psndoc where to_date(enddate,'yyyy-mm-dd') > sysdate and (";

	private final static String INSERT_TBM_AWAYB_SQL_STR = "insert into tbm_awayb(  aheadfee, ts, awayaddress, awaybeginday, awaybegintime, awayendday, awayendtime, awaynote, awaypsnorder, awaysumhour, pk_away_b, pk_away_h, pk_tbm_psndoc, psncode) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private final static String INSERT_TBM_AWAYH_SQL_STR = "insert into tbm_awayh ( applier, applydate, applydept,approvestatus, awayid, awaytype, billname, isappbill, ishrssbill,pk_away_h,pk_corp, ts ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private AwayFormBean awayFormBean;

	private List<String> pk_psndocList;

	public boolean insertChuChaiInfo(String accountID) {

		boolean success = false;

		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

			findPersonInfo(conn);

			if (pk_psndocList != null) {

				findPk_tbm_psndoc(conn);

				if (this.awayFormBean.getPersonList() != null) {

					conn.setAutoCommit(false);

					insertTbm_awayh(conn);

					insertTbm_awayb(conn,accountID);

					conn.commit();

					success = true;
				} else {
					log.error(new StringBuilder().append(
							"出差人员还未排班，出差数据写入HR失败。表单名称为：").append(
							this.awayFormBean.getBillname()));
				}

			} else {
				log.error(new StringBuilder().append("工号填写不正确，导出失败。表单名称为：")
						.append(this.awayFormBean.getBillname()));
			}

		} catch (Exception e) {
			DBUtil.rollback(conn);
			StringBuilder sb = new StringBuilder().append("表单《")
					.append(awayFormBean.getBillname())
					.append("》数据写入HR数据库失败，单据编号为：")
					.append(this.awayFormBean.getAwayid());
			log.error(sb, e);
		//	System.out.println(sb);
			e.printStackTrace();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(conn);
		}
		awayFormBean = null;
		if (pk_psndocList != null)
			pk_psndocList.clear();
		pk_psndocList = null;
		return success;
	}

	private void insertTbm_awayb(Connection conn, String accountID) throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			// insert into tbm_awayb( aheadfee, ts, awayaddress, awaybeginday,
			// awaybegintime, awayendday, awayendtime, awaynote, awaypsnorder,
			// awaysumhour, pk_away_b, pk_away_h, pk_tbm_psndoc, psncode) values
			// (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			pst = conn.prepareStatement(INSERT_TBM_AWAYB_SQL_STR);

			for (int i = 0, size = this.awayFormBean.getPersonList().size(); i < size; i++) {
				pst.setString(1, this.awayFormBean.getAheadfee());
				pst.setString(2, this.awayFormBean.getTs());
				pst.setString(3, this.awayFormBean.getAwayaddress());
				pst.setString(4, this.awayFormBean.getAwaybeginday());
				pst.setString(5, OA2HRConstant.getWorkTime(
						this.awayFormBean.getAwaybegintime(),
						this.awayFormBean.getPk_corp(), accountID));
				
								
				pst.setString(6, this.awayFormBean.getAwayendday());
				pst.setString(7, OA2HRConstant.getWorkTime(
						this.awayFormBean.getAwayendtime(),					
						this.awayFormBean.getPk_corp(), accountID));
				
//				System.err.println("下午时间："+OA2HRConstant.getWorkTime(
//						this.awayFormBean.getAwayendtime(),
//						this.awayFormBean.getPk_corp(), accountID));
//				System.err.println("获取时间"+this.awayFormBean.getAwayendtime());
				
				
				pst.setString(
						8,
						MyUtil.getLimitLengthString(
								this.awayFormBean.getAwaynote(), 500, "…"));
				pst.setString(9, "0");
				pst.setString(10, this.awayFormBean.getAwaysumhour());
				pst.setString(11, MyUtil.createUUID());
				pst.setString(12, this.awayFormBean.getPk_away_h());
				pst.setString(13, this.awayFormBean.getPersonList().get(i)
						.getPk_tbm_psndoc());
				pst.setString(14, this.awayFormBean.getPersonList().get(i)
						.getPsncode());

				pst.addBatch();
			}

			pst.executeBatch();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
		}
	}

	private void insertTbm_awayh(Connection conn) throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			// insert into tbm_awayh ( applier, applydate,
			// applydept,approvestatus, awayid, awaytype, billname, isappbill,
			// ishrssbill,pk_away_h,pk_corp, ts ) values ( ?, ?, ?, ?, ?, ?, ?,
			// ?, ?, ?, ?, ?)

			pst = conn.prepareStatement(INSERT_TBM_AWAYH_SQL_STR);
			awayFormBean.setPk_away_h(MyUtil.createUUID());
			pst.setString(1, this.awayFormBean.getApplier());
			pst.setString(2, this.awayFormBean.getApplydate());
			pst.setString(3, this.awayFormBean.getApplydept());
			pst.setString(4, "200");
			pst.setString(5, String.valueOf(this.awayFormBean.getSummaryId()));
			pst.setString(6, this.awayFormBean.getAwaytype());
			pst.setString(7, MyUtil.getLimitLengthString(
					this.awayFormBean.getBillname(), 50, "…"));
			pst.setString(8, "N");
			pst.setString(9, "N");
			pst.setString(10, this.awayFormBean.getPk_away_h());
			pst.setString(11, this.awayFormBean.getPk_corp());
			pst.setString(12, this.awayFormBean.getTs());

			pst.executeUpdate();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
		}
	}

	private void findPersonInfo(Connection conn) throws Exception {
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			// "select t.pk_psndoc, t.pk_deptdoc,t.pk_corp
			// from bd_psndoc t where psncode=? and psnclscope='0'";
			// pst = conn.prepareStatement(FIND_PERSONINFO_STR);

			StringBuilder sb = new StringBuilder().append(FIND_PERSONINFO_STR)
					.append(" ( ");

			for (int i = 0, size = awayFormBean.getPersonCodeList().size(); i < size; i++) {

				if (i != 0) {
					sb.append(" or ");
				}
				sb.append(" psncode=? ");
			}

			sb.append(" ) ");

			st = conn.prepareStatement(sb.toString());
			sb = null;
			for (int i = 0, size = awayFormBean.getPersonCodeList().size(); i < size; i++) {
				st.setString(i+1,awayFormBean.getPersonCodeList().get(i));
			}
			boolean first = true;
			rs = st.executeQuery();
			while (rs.next()) {

				if (pk_psndocList == null) {
					pk_psndocList = new ArrayList<String>();
				}

				pk_psndocList.add(rs.getString(1));

				if (first) {
					first = false;
					this.awayFormBean.setApplydept(rs.getString(2));
					this.awayFormBean.setPk_corp(rs.getString(3));
				}
			}
		} finally {
			DBUtil.close(rs);
			DBUtil.close(st);
		}
	}

	private void findPk_tbm_psndoc(Connection conn) throws Exception {
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			// "select pk_tbm_psndoc, pk_psndoc from tbm_psndoc where
			// pk_psndoc=?";
			StringBuilder sb = new StringBuilder();
			sb.append(FIND_PK_TBM_PSNDOC_STR);

			for (int i = 0, size = this.pk_psndocList.size(); i < size; i++) {

				if (i != 0) {
					sb.append(" or ");
				}
				sb.append(" pk_psndoc=? ");
			}
			sb.append(")");
			st = conn.prepareStatement(sb.toString());
			for (int i = 0, size = this.pk_psndocList.size(); i < size; i++) {
				st.setString(i+1,pk_psndocList.get(i));
			}
			sb = null;
			rs = st.executeQuery();
			while (rs.next()) {
				awayFormBean.addPersonBean(rs.getString(1), rs.getString(2));
			}
		} finally {
			DBUtil.close(rs);
			DBUtil.close(st);
		}
	}

	public void setChuChaiBean(AwayFormBean chuChaiBean) {
		this.awayFormBean = chuChaiBean;
	}

	public static void main(String[] args) throws Exception {
		
		
		String temp = "取字符串的前toCount个字符";
		//System.out.println(MyUtil.getLimitLengthString(temp, 36, "…"));
	}

}
